package fr.uniscol.dao.impl;

import fr.uniscol.dao.IReferenceMatieresDao;
import fr.uniscol.dao.UniscolDaoSupport;

import static fr.uniscol.dao.impl.SQLNames.*;
import fr.uniscol.dao.mapper.MEFRowMapper;
import fr.uniscol.dao.mapper.MatiereRowMapper;
import fr.uniscol.dao.mapper.ModaliteElectionRowMapper;
import fr.uniscol.model.BaseAnneeScolaire;
import fr.uniscol.model.MEF;
import fr.uniscol.model.Matiere;
import fr.uniscol.model.ProgrammeMEF;
import fr.uniscol.siecle.model.ModaliteElection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.stereotype.Repository;

@Repository("referenceMatieresDao")
public class ReferenceMatieresDaoImpl extends UniscolDaoSupport implements IReferenceMatieresDao {

	private Map<String, List<ProgrammeMEF>> programmesMefs = null;
	
	private List<ModaliteElection> modalites = null;
	
	private List<MEF> mefs = null;
	 
	@Override
	public List<ProgrammeMEF> findAllProgrammesByMEF(MEF mef) {
		
		if(programmesMefs == null){
			programmesMefs = getAllProgrammesMefs();
		}
		
		List<ProgrammeMEF> programmes = programmesMefs.get(mef.getId() + "");
		if(programmes == null){
			programmes = new ArrayList<ProgrammeMEF>();
		}
		
		return programmes;
	}

	private Map<String, List<ProgrammeMEF>> getAllProgrammesMefs() {
		
		Map<String, List<ProgrammeMEF>> programmes = getJdbcTemplate().query(
                                  "SELECT p.modalite, "
                                      + " m.matiereid, m.libelle libelle_matiere, m.libelle_long libelle_long_matiere,"
                                      + " mf.mefid, mf.libelle_court libelle_court_mef, mf.libelle_long libelle_long_mef "
								+ "FROM uni_programme p, uni_matiere m, uni_mef mf "
								+ "WHERE p.matiereid=m.matiereid "
								+ "AND mf.mefid=p.mefid ", 
            
                new ResultSetExtractor<Map<String, List<ProgrammeMEF>>>(){

                    @Override
                    public Map<String, List<ProgrammeMEF>> extractData(ResultSet rs)
                    throws SQLException, DataAccessException {

                        Map<String, List<ProgrammeMEF>> prg = new HashMap<String, List<ProgrammeMEF>>();

                        String codeMef=  null;
                        List<ProgrammeMEF> programmes = null;
                        ProgrammeMEF programme = null;
                        Matiere matiere = null;
                        MEF mef = null;
                        
                        while(rs.next()){
                            codeMef = rs.getString("MEFID");

                            programmes = prg.get(codeMef);
                            if(programmes == null){
                                programmes = new ArrayList<ProgrammeMEF>();
                                prg.put(codeMef, programmes);
                            }
                            
                            programme = new ProgrammeMEF();
                            
                            matiere = new Matiere();
                            matiere.setId(rs.getLong("MATIEREID"));
                            matiere.setLibelle(rs.getString("LIBELLE_MATIERE"));
                            matiere.setLibelleLong(rs.getString("LIBELLE_LONG_MATIERE"));
                            programme.setMatiere(matiere);
                            
                            mef = new MEF();
                            mef.setId(rs.getLong("MEFID"));
                            mef.setLibelleCourt(rs.getString("LIBELLE_COURT_MEF"));
                            mef.setLibelleLong(rs.getString("LIBELLE_LONG_MEF"));
                            programme.setMef(mef);
                            
                            programme.setModalite(rs.getString("MODALITE"));
                            
                            programmes.add(programme);
                        }

                        return prg;
                    }
                }); 
		
		return programmes;
	}

	@Override
	public List<ModaliteElection> findAllModalites(BaseAnneeScolaire base) {
		
		if(modalites == null){
			modalites = getAllModalites(base);
		}
		
		return modalites;
	}

	private List<ModaliteElection> getAllModalites(BaseAnneeScolaire base) {
		
		List<ModaliteElection> modalites = getJdbcTemplate().query("SELECT * FROM bee_modalite WHERE etablissementid=? AND annee=?", new Object[]{
			base.getEtablissement().getId(),
			base.getAnnee()
		}, new ModaliteElectionRowMapper());		
		
		return modalites;
	}

	@Override
	public List<MEF> findAllMEFAvailables(BaseAnneeScolaire base) {
		
		if(mefs == null){
			mefs = getAllMefs(base);
		}
		
		return mefs;
	}

	private List<MEF> getAllMefs(BaseAnneeScolaire base) {
		
		List<MEF> tmpMefs = getJdbcTemplate().query("SELECT * FROM uni_mef WHERE baseid=? ", new Object[]{
			base.getId(),
		}, new MEFRowMapper());
		
		return tmpMefs;
	}

    @Override
    public MEF getMEFById(int mefid) {
       
        MEF mef = getJdbcTemplate().queryForObject("SELECT * FROM uni_mef WHERE mefid=? ", new Object[]{mefid}, new MEFRowMapper());
        
        return mef;
    }

    @Override
    public List<Matiere> findAllMatieres(BaseAnneeScolaire baseSelectionnee) {
        
        
        List<Matiere> matieres = getJdbcTemplate().query("SELECT * FROM " + TABLE_MATIERE + " WHERE baseid=? ", new Object[]{
            baseSelectionnee.getId()
        }, 
        new MatiereRowMapper());
        
        return matieres;
    }
}
